VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   2715
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   4770
   LinkTopic       =   "Form1"
   ScaleHeight     =   2715
   ScaleWidth      =   4770
   StartUpPosition =   3  'Windows Default
   Begin VB.TextBox txtCurrDate 
      Height          =   375
      Left            =   240
      TabIndex        =   3
      Text            =   "2006/12/19"
      Top             =   1080
      Width           =   2175
   End
   Begin VB.TextBox txtConn 
      Height          =   375
      Left            =   240
      TabIndex        =   2
      Text            =   "Provider=SQLOLEDB;Driver={SQL Server};Server=blueprnt-dev2;Initial Catalog=keytax;User ID=sa; Password=blueprnt"
      Top             =   120
      Width           =   4335
   End
   Begin VB.TextBox txtFileName 
      Height          =   375
      Left            =   240
      TabIndex        =   1
      Text            =   "C:\Projects\Keytax Test\Keytaxtestdata.csv"
      Top             =   600
      Width           =   4335
   End
   Begin VB.CommandButton cmdStart 
      Caption         =   "Start Insert"
      Height          =   615
      Left            =   2880
      TabIndex        =   0
      Top             =   1920
      Width           =   1575
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Private Sub cmdStart_Click()
Dim sFilename As String
Dim sTemp
Dim sSQL As String
Dim sSQLConst As String
Dim sConnection As String
Dim sMaxNumber
Dim rs As ADODB.Recordset
Dim nNumRecords

sConnection = txtConn.Text
sFilename = txtFileName.Text

Set rs = New ADODB.Recordset

nNumRecords = 0

sSQLConst = "INSERT INTO dir_response "
sSQLConst = sSQLConst & "(req_seq_num, request_status, emailed_yn, sars_appl_id, created_date, created_by, income_tax_area_code, directive_number, decline_reason,"
sSQLConst = sSQLConst & "issue_date, dir_start_date, dir_end_date, tax_percentage, tax_free_portion, gross_amount_paye, deduction_frequency, vat_registration_number,"
sSQLConst = sSQLConst & "vat_amount, paye_amt, emailed_on, statement_no, scod_dir_type, assessed_tax_amt, it88_ref_no, cancel_process_status, cancel_status,"
sSQLConst = sSQLConst & "ALLOWED_CONTRIB, APPROVED_DEEMED_RENUMERATION)"
sSQLConst = sSQLConst & "VALUES     ("

'Connection
sSQL = "SELECT     MAX(directive_number) AS Expr1 From dir_response"
Set rs = Get_Recordset(sSQL, sConnection)

sMaxNumber = rs("Expr1").Value

'Tina: added 2007/01/26
sSQL = "SELECT     MAX(IT88_ref_no) AS Expr2 From dir_response"
Set rs = Get_Recordset(sSQL, sConnection)

sMaxIT88Ref = rs("Expr2").Value

Set rs = Nothing

Open sFilename For Input As #1


    'Line Input #1, sLine
    Do Until EOF(1)
    'Do Until sLine = ""
        Line Input #1, sLine
        sTemp = Split(sLine, ",")
        sCurrDate = txtCurrDate.Text
        sSQL = ""
        sSQL = sSQL & sTemp(6) & ",'07','N', " & sMaxNumber & ", '" & sCurrDate & "', 'TKInsert', '0084', 3129641, NULL, '" & sCurrDate & "', '" & sCurrDate & "', '" & sCurrDate & "', NULL, NULL,"
        sSQL = sSQL & sTemp(7) & " , NULL, NULL, NULL, NULL, NULL, NULL, 'LS', " & sTemp(8) & ", "
        'Tina: added 2007/01/30
        If sTemp(8) = 0 Then
            sSQL = sSQL & "NULL, NULL, NULL, NULL, NULL)"
        Else
            sSQL = sSQL & sMaxIT88Ref & ", NULL, NULL, NULL, NULL)"
        End If
        sSQL = sSQLConst & sSQL
        Call Update_Record(sSQL, sConnection)
        sMaxNumber = sMaxNumber + 1
        sMaxIT88Ref = sMaxIT88Ref + 1
    '    Line Input #1, sLine
        nNumRecords = nNumRecords + 1
    Loop
    
Close #1

MsgBox ("Number of records inserted: " & nNumRecords)
End

End Sub

